const db = require("../config/sqlserverConfig");
const mydb = require("../config/mysqlConfig");
class TestDemo {
  // 库存表
  async getInventoryData(req, res, next) {
    try {
      var sql = `SELECT * FROM 库存表`;
      const data = await db(sql);
      var values = "";
      var test = [];
      for (let i = 0; i < data.recordset.length; i++) {
        data.recordset[i].规格型号 = data.recordset[i].规格型号.replace(
          /\\/g,
          "/"
        );
        values += `(${data.recordset[i].序号},'${data.recordset[i].物料编码}','${data.recordset[i].物料名称}','${data.recordset[i].仓库名称}','${data.recordset[i].仓位}','${data.recordset[i].批号}','${data.recordset[i].库存主单位}',${data.recordset[i].库存量},'${data.recordset[i].库存组织}',${data.recordset[i].调拨数量},${data.recordset[i].可调拨数量},'${data.recordset[i].PA}','${data.recordset[i].规格型号}','${data.recordset[i].申请日期}'),`;
      }
      values = values.slice(0, -1);
      const sql2 = `INSERT INTO inventorytable (SerialNumber,MaterialCode,MaterialName,WarehouseName,ShippingSpace,BatchNumber,Stockholder,Stock,InventoryOrganization,TransferQuantity,cTransferQuantity,PA,SpecificationsModels,FilingDate) VALUES ${values}`;
      await mydb(sql2);
      res.send({
        data: "库存表成功转移数据",
      });
    } catch (error) {
      if (error) {
        res.send({
          data: "数据已添加",
        });
      }
    }
  }
  // MOQ表
  async getMOQdata(req, res, next) {
    try {
      var sql = `SELECT * FROM MOQ`;
      const data = await db(sql);
      var values = "";
      for (let i = 0; i < data.recordset.length; i++) {
        values += `(${data.recordset[i].序号},'${data.recordset[i].物料编码}',${data.recordset[i].起订量},'${data.recordset[i].名称}','${data.recordset[i].责任人}'),`;
      }
      values = values.slice(0, -1);
      const sql2 = `INSERT INTO moq (SerialNumber,MaterialCode,MinimumOrder,Designation,DutyOfficer) VALUES ${values}`;
      await mydb(sql2);
      res.send({
        data: "MOQ表成功转移数据",
      });
    } catch (error) {
      res.send({
        data: "数据已添加",
      });
    }
  }
  // 操作履历记录
  async getoperatinghistoryData(req, res, next) {
    try {
      var sql = `SELECT * FROM 操作履历`;
      const data = await db(sql);
      var values = "";
      for (let i = 0; i < data.recordset.length; i++) {
        var date = new Date(data.recordset[i].时间);
        data.recordset[i].时间 =
          date.getFullYear() +
          "-" +
          (date.getMonth() + 1) +
          "-" +
          date.getDate() +
          " " +
          date.getHours() +
          ":" +
          date.getMinutes() +
          ":" +
          date.getSeconds();
        values += `(${data.recordset[i].序号},'${data.recordset[i].姓名}','${data.recordset[i].操作内容}','${data.recordset[i].时间}'),`;
      }
      values = values.slice(0, -1);
      const sql2 = `INSERT INTO operatinghistory (SerialNumber,Name,OperationalContext,Time) VALUES ${values}`;
      await mydb(sql2);
      res.send({
        data: "操作履历表成功转移数据",
      });
    } catch (error) {
      res.send({
        data: "数据已添加",
      });
    }
  }
  // 调拨记录表格
  async gettransferrecordData(req, res, next) {
    try {
      var sql = `SELECT * FROM 调拨记录`;
      const data = await db(sql);
      var values = "";
      for (let i = 0; i < data.recordset.length; i++) {
        var date = new Date(data.recordset[i].调拨时间);
        data.recordset[i].调拨时间 =
          date.getFullYear() +
          "-" +
          (date.getMonth() + 1) +
          "-" +
          date.getDate() +
          " " +
          date.getHours() +
          ":" +
          date.getMinutes() +
          ":" +
          date.getSeconds();
        values += `(${data.recordset[i].序号},'${data.recordset[i].物料编码}','${data.recordset[i].仓库名称}','${data.recordset[i].仓位}','${data.recordset[i].批号}','${data.recordset[i].项目编号}',${data.recordset[i].调拨数量},'${data.recordset[i].调拨时间}','${data.recordset[i].申请日期}'),`;
      }
      values = values.slice(0, -1);
      const sql2 = `INSERT INTO transferrecord (SerialNumber,MaterialCode,WarehouseName,ShippingSpace,BatchNumber,ItemNumber,TransferQuantity,TransferTime,FilingDate) VALUES ${values}`;
      await mydb(sql2);
      res.send({
        data: "调拨记录表成功转移数据",
      });
    } catch (error) {
      console.log(error);
      res.send({
        data: "数据已添加",
      });
    }
  }
  // 订单需求表
  async getcustomizeData(req, res, next) {
    try {
      var sql = `SELECT * FROM 订单需求`;
      const data = await db(sql);
      var values = "";
      for (let i = 0; i < data.recordset.length; i++) {
        data.recordset[i].规格型号 = data.recordset[i].规格型号.replace(
          /\'/g,
          "-"
        );
        data.recordset[i].参数说明 = data.recordset[i].参数说明.replace(
          /\'/g,
          "."
        );
        var date = new Date(data.recordset[i].时间);
        data.recordset[i].时间 =
          date.getFullYear() +
          "-" +
          (date.getMonth() + 1) +
          "-" +
          date.getDate() +
          " " +
          date.getHours() +
          ":" +
          date.getMinutes() +
          ":" +
          date.getSeconds();
        values += `(${data.recordset[i].序号},'${data.recordset[i].时间}','${data.recordset[i].BC}','${data.recordset[i].PA}','${data.recordset[i].项目号}','${data.recordset[i].物料类型}','${data.recordset[i].物料编码}','${data.recordset[i].物料名称}','${data.recordset[i].规格型号}','${data.recordset[i].参数说明}','${data.recordset[i].单台用量}','${data.recordset[i].单位}','${data.recordset[i].固定损耗}','${data.recordset[i].总需求}','${data.recordset[i].备注}','${data.recordset[i].调拨数量}','${data.recordset[i].仓库名称}','${data.recordset[i].仓位}','${data.recordset[i].批号}','${data.recordset[i].下单数量}','${data.recordset[i].状态}','${data.recordset[i].MC}'),`;
      }
      values = values.slice(0, -1);
      const sql2 = `INSERT INTO orderrequest 
    (SerialNumber,
      Time,
      BC,
      PA,
      ItemNumber,
      MaterialType,
      MaterialCode,
      MaterialName,
      SpecificationsModels,
      ParameterDescription,
      AmountUsed,
      Unit,
      FixedLoss,
      TotalDemand,
      Remark,
      TransferQuantity,
      WarehouseName,
      ShippingSpace,
      BatchNumber,
      NumberOrders,
      state,
      MC
      ) 
    VALUES ${values}`;
      await mydb(sql2);
      res.send({
        data: "订单需求表表成功转移数据",
      });
    } catch (error) {
      console.log(error);
      res.send({
        data: "错误",
      });
    }
  }
  // 生产订单表
  async getproductionData(req, res, next) {
    try {
      var sql = `SELECT * FROM 生产订单表`;
      const data = await db(sql);
      var values = "";
      for (let i = 0; i < data.recordset.length; i++) {
        var date = new Date(data.recordset[i].申请日期);
        data.recordset[i].申请日期 =
          date.getFullYear() +
          "-" +
          (date.getMonth() + 1) +
          "-" +
          date.getDate();

        var date = new Date(data.recordset[i].到货日期);
        data.recordset[i].到货日期 =
          date.getFullYear() +
          "-" +
          (date.getMonth() + 1) +
          "-" +
          date.getDate() +
          " " +
          date.getHours() +
          ":" +
          date.getMinutes() +
          ":" +
          date.getSeconds();
        values += `
            (${data.recordset[i].序号},
          '${data.recordset[i].物料编码}',
          '${data.recordset[i].物料名称}',
          '${data.recordset[i].单据编号}',
          '${data.recordset[i].仓库名称}',
          '${data.recordset[i].申请日期}',
          '${data.recordset[i].到货日期}',
          '${data.recordset[i].批号}',
          '${data.recordset[i].库存主单位}',
          ${data.recordset[i].申请数量},
          '${data.recordset[i].BC}',
          '${data.recordset[i].PA}',
          ${data.recordset[i].调拨数量},
          ${data.recordset[i].可调拨数量},
          '${data.recordset[i].仓位}',
          ${data.recordset[i].库存量}),`;
      }
      values = values.slice(0, -1);
      const sql2 = `INSERT INTO productionorder 
    (SerialNumber,
      MaterialCode,
      MaterialName,
      ReceiptNumber,
      WarehouseName,
      ReuestDate,
      ArriveDate,
      BatchNumber,
      Stockholder,
      ApplicationNum,
      BC,
      PA,
      TransferQuantity,
      cTransferQuantity,
      ShippingSpace,
      Stock
      ) 
    VALUES ${values}`;
      await mydb(sql2);
      res.send({
        data: "生产订单表成功转移数据",
      });
    } catch (error) {
      console.log(error);
      res.send({
        data: "错误",
      });
    }
  }
  // 生产辅料
  async getmaterialsData(req, res, next) {
    try {
      var sql = `SELECT * FROM 生产辅料`;
      const data = await db(sql);
      var values = "";
      for (let i = 0; i < data.recordset.length; i++) {
        values += `(${data.recordset[i].序号},'${data.recordset[i].物料类型}','${data.recordset[i].物料编号}','${data.recordset[i].物料名称}','${data.recordset[i]["规格型号/图号"]}','${data.recordset[i].参数说明}','${data.recordset[i].品牌}','${data.recordset[i].单位}','${data.recordset[i].安全库存}','${data.recordset[i].旧安全库存_物料金额}','${data.recordset[i]["2020年_总领料数量"]}','${data.recordset[i]["2020年_月平均领料数量"]}','${data.recordset[i]["2021年_总领料数量"]}','${data.recordset[i]["2021年_月平均领料数量"]}','${data.recordset[i].两年_月平均领料数量}','${data.recordset[i].最新安全库存}','${data.recordset[i].单价}','${data.recordset[i].金额}'),`;
      }
      values = values.slice(0, -1);
      const sql2 = `INSERT INTO production_auxiliary_materials (SerialNumber,MaterialType,MaterialCode,MaterialName,SpecificationsModelsAndPic,ParameterDescription,brand,Unit,SafeStock,OldeStock_sum,TwentyStockRequisitionTotal,TwentyStockRequisitionMouth,TwentyoneStockRequisitionTotal,TwentyoneStockRequisitionMouth,TwoStockRequisition,NewStock,UnitPrice,Money) 
    VALUES ${values}`;
      await mydb(sql2);
      res.send({
        data: "生产订单表成功转移数据",
      });
    } catch (error) {
      console.log(error);
      res.send({
        data: values,
      });
    }
  }
  // 生产辅料1
  async getmaterialsData1(req, res, next) {
    try {
      var sql = `SELECT * FROM 生产辅料1`;
      const data = await db(sql);
      var values = "";
      for (let i = 0; i < data.recordset.length; i++) {
        values += `(${data.recordset[i].序号},'${data.recordset[i].物料类型}','${data.recordset[i].物料编号}','${data.recordset[i].物料名称}','${data.recordset[i]["规格型号/图号"]}','${data.recordset[i].参数说明}','${data.recordset[i].品牌}','${data.recordset[i].单位}','${data.recordset[i].安全库存}','${data.recordset[i].旧安全库存_物料金额}','${data.recordset[i]["2020年_总领料数量"]}','${data.recordset[i]["2020年_月平均领料数量"]}','${data.recordset[i]["2021年_总领料数量"]}','${data.recordset[i]["2021年_月平均领料数量"]}','${data.recordset[i].两年_月平均领料数量}','${data.recordset[i].最新安全库存}','${data.recordset[i].单价}','${data.recordset[i].金额}'),`;
      }
      values = values.slice(0, -1);
      const sql2 = `INSERT INTO production_auxiliary_materials_1 (SerialNumber,MaterialType,MaterialCode,MaterialName,SpecificationsModelsAndPic,ParameterDescription,brand,Unit,SafeStock,OldeStock_sum,TwentyStockRequisitionTotal,TwentyStockRequisitionMouth,TwentyoneStockRequisitionTotal,TwentyoneStockRequisitionMouth,TwoStockRequisition,NewStock,UnitPrice,Money) 
      VALUES ${values}`;
      await mydb(sql2);
      res.send({
        data: "生产订单表1成功转移数据",
      });
    } catch (error) {
      console.log(error);
      res.send({
        data: values,
      });
    }
  }
  // 委外表
  async getveneerData(req, res, next) {
    try {
      var sql = `SELECT * FROM 委外表`;
      const data = await db(sql);
      var values = "";
      for (let i = 0; i < data.recordset.length; i++) {
        var date = new Date(data.recordset[i].申请日期);
        data.recordset[i].申请日期 =
          date.getFullYear() +
          "-" +
          (date.getMonth() + 1) +
          "-" +
          date.getDate();

        var date = new Date(data.recordset[i].到货日期);
        data.recordset[i].到货日期 =
          date.getFullYear() +
          "-" +
          (date.getMonth() + 1) +
          "-" +
          date.getDate() +
          " " +
          date.getHours() +
          ":" +
          date.getMinutes() +
          ":" +
          date.getSeconds();
        values += `
              (${data.recordset[i].序号},
            '${data.recordset[i].物料编码}',
            '${data.recordset[i].物料名称}',
            '${data.recordset[i].单据编号}',
            '${data.recordset[i].仓库名称}',
            '${data.recordset[i].申请日期}',
            '${data.recordset[i].到货日期}',
            '${data.recordset[i].批号}',
            '${data.recordset[i].库存主单位}',
            ${data.recordset[i].申请数量},
            '${data.recordset[i].BC}',
            '${data.recordset[i].PA}',
            ${data.recordset[i].调拨数量},
            ${data.recordset[i].可调拨数量},
            '${data.recordset[i].仓位}',
            ${data.recordset[i].库存量}),`;
      }
      values = values.slice(0, -1);
      const sql2 = `INSERT INTO veneer 
      (SerialNumber,
        MaterialCode,
        MaterialName,
        ReceiptNumber,
        WarehouseName,
        FilingDate,
        ArriveDate,
        BatchNumber,
        Stockholder,
        ApplicationNum,
        BC,
        PA,
        TransferQuantity,
        cTransferQuantity,
        ShippingSpace,
        Stock
        ) 
      VALUES ${values}`;
      await mydb(sql2);
      res.send({
        data: "委外表成功转移数据",
      });
    } catch (error) {
      console.log(error);
      res.send({
        data: "错误",
      });
    }
  }
  // 在途表
  async getonwayData(req, res, next) {
    try {
      var sql = `SELECT * FROM 在途表`;
      const data = await db(sql);
      var values = "";
      for (let i = 0; i < data.recordset.length; i++) {
        var date = new Date(data.recordset[i].申请日期);
        data.recordset[i].申请日期 =
          date.getFullYear() +
          "-" +
          (date.getMonth() + 1) +
          "-" +
          date.getDate();

        var date = new Date(data.recordset[i].到货日期);
        data.recordset[i].到货日期 =
          date.getFullYear() +
          "-" +
          (date.getMonth() + 1) +
          "-" +
          date.getDate() +
          " " +
          date.getHours() +
          ":" +
          date.getMinutes() +
          ":" +
          date.getSeconds();
        values += `
              (${data.recordset[i].序号},
            '${data.recordset[i].物料编码}',
            '${data.recordset[i].物料名称}',
            '${data.recordset[i].单据编号}',
            '${data.recordset[i].仓库名称}',
            '${data.recordset[i].申请日期}',
            '${data.recordset[i].到货日期}',
            '${data.recordset[i].批号}',
            '${data.recordset[i].库存主单位}',
            ${data.recordset[i].申请数量},
            '${data.recordset[i].BC}',
            '${data.recordset[i].PA}',
            ${data.recordset[i].调拨数量},
            ${data.recordset[i].可调拨数量},
            '${data.recordset[i].仓位}',
            ${data.recordset[i].库存量}),`;
      }
      values = values.slice(0, -1);
      const sql2 = `INSERT INTO transit_list 
      (SerialNumber,
        MaterialCode,
        MaterialName,
        ReceiptNumber,
        WarehouseName,
        FilingDate,
        ArriveDate,
        BatchNumber,
        Stockholder,
        ApplicationNum,
        BC,
        PA,
        TransferQuantity,
        cTransferQuantity,
        ShippingSpace,
        Stock
        ) 
      VALUES ${values}`;
      await mydb(sql2);
      res.send({
        data: "在途表表成功转移数据",
      });
    } catch (error) {
      console.log(error);
      res.send({
        data: "错误",
      });
    }
  }
  // 生产领料
  async getmaterialreqData(req, res, next) {
    try {
      var sql = `SELECT * FROM 生产领料单`;
      const data = await db(sql);
      var values = "";
      for (let i = 0; i < data.recordset.length; i++) {
        var date = new Date(data.recordset[i].日期);
        data.recordset[i].日期 =
          date.getFullYear() +
          "-" +
          (date.getMonth() + 1) +
          "-" +
          date.getDate();

        var date = new Date(data.recordset[i].创建日期);
        data.recordset[i].创建日期 =
          date.getFullYear() +
          "-" +
          (date.getMonth() + 1) +
          "-" +
          date.getDate() +
          " " +
          date.getHours() +
          ":" +
          date.getMinutes() +
          ":" +
          date.getSeconds();
        values += `
              (${data.recordset[i].序号},
            '${data.recordset[i].单据编号}',
            '${data.recordset[i]["项目号#"]}',
            '${data.recordset[i].日期}',
            '${data.recordset[i].创建日期}',
            '${data.recordset[i].物料编码}',
            '${data.recordset[i].实发数量}',
            '${data.recordset[i].领料人}',
            '${data.recordset[i].领料部门}',
            '${data.recordset[i].仓位}'),`;
      }
      values = values.slice(0, -1);
      const sql2 = `INSERT INTO material_requisition 
      (SerialNumber,
        ReceiptNumber,
        ProjectNo,
        Date,
        CreateDate,
        MaterialCode,
        actualNumber,
        Materialor,
        MaterialDep,
        ShippingSpace
        ) 
      VALUES ${values}`;
      await mydb(sql2);
      res.send({
        data: "生产领料表成功转移数据",
      });
    } catch (error) {
      console.log(error);
      res.send({
        data: "错误",
      });
    }
  }
}

module.exports = new TestDemo();
